﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using VswService_V7.Utility;
using VswService_V7.DataBase.Tables.L2INTERFACE;

namespace VswService_V7.DataBase.Tables
{
    /// <summary>
    /// wp code
    /// </summary>
    public partial class DOWNTIME_TABLE
    {
        #region 1123 wp 加入 DOWNTIME
        public static int pageNum = 20;

        public static string updateDowntime = "4001";//modify downtime record
        public static string addDowntime = "4002";//split downtime record
                                                  /// <summary>
                                                  /// select downtime record of the first page
                                                  /// </summary>
                                                  /// <param name="totalPages"></param>
                                                  /// <returns></returns>
        public static DataTable InnitPageDownTime(out string totalPages)
        {
            //start/end time of the day
            try
            {
                string nowDayStartTime = DateTime.Now.ToString(Formats.GetShortFormat()) + " 00:00:00";
                string nowDayEndTime = DateTime.Now.ToString(Formats.GetDateFormat());
                string downTimeSql = string.Format("select ROWNUM rn,d.* from (select * from {0} where {1}>'{2}' and {3}<'{4}' order by {5} desc) d", tableName, fd_startTime, nowDayStartTime, fd_startTime, nowDayEndTime, fd_updateTime);
                string sql = string.Format("select * from ({0}) where rn>{1} and rn<={2}",
                  downTimeSql, 0, pageNum);
                string sqlCount = string.Format("select count(*) from ({0})",
                   downTimeSql);
                DataTable Table = OracleHelper.Exct_QueryTable(sql);
                if (Table != null)
                {
                    int Pages = Convert.ToInt32(TASK_INFO.GetTableCount(sqlCount));
                    if (Pages % pageNum == 0)
                    {
                        totalPages = (Pages / pageNum).ToString();
                    }
                    else
                    {
                        totalPages = (Pages / pageNum + 1).ToString();
                    }
                    LogHelper.WriteLog("InnitPageDownTime Success");
                    return Table;
                }
                else
                {
                    totalPages = "";
                    return null; ;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("InnitPageDownTime", ex);
                throw ex;
            }
        }
        /// <summary>
        /// select downtime record of the certain page
        /// </summary>
        /// <param name="sqldowntime"></param>
        /// <param name="minRow"></param>
        /// <param name="maxRow"></param>
        /// <param name="totalPages"></param>
        /// <returns></returns>
        public static DataTable SelectV7_DOWNTIME(string sqldowntime, int minRow, int maxRow, out string totalPages)
        {
            try
            {
                string sql = string.Format("select * from ({0}) where rn>{1} and rn<={2}",
                  sqldowntime, minRow, maxRow);
                string sqlCount = string.Format("select count(*) from ({0})",
                   sqldowntime);
                DataTable Table = OracleHelper.Exct_QueryTable(sql);
                if (Table != null)
                {
                    int Pages = Convert.ToInt32(TASK_INFO.GetTableCount(sqlCount));
                    if (Pages % pageNum == 0)
                    {
                        totalPages = (Pages / pageNum).ToString();
                    }
                    else
                    {
                        totalPages = (Pages / pageNum + 1).ToString();
                    }
                    LogHelper.WriteLog("SelectV7_DOWNTIME Success");
                    return Table;
                }
                else
                {
                    totalPages = "";
                    return null; ;
                }

            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("SelectV7_DOWNTIME", ex);
                throw ex;
            }
        }
        /// <summary>
        /// update downtime record
        /// </summary>
        /// <param name="downtimeno"></param>
        /// <param name="stoptype"></param>
        /// <param name="machineno"></param>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <param name="serviceteam"></param>
        /// <param name="stopreson"></param>
        /// <param name="status"></param>
        /// <param name="downcommom"></param>
        /// <param name="operatename"></param>
        /// <param name="userno"></param>
        /// <returns></returns>
        public static DataTable Updatedowntime(string downtimeno, string stoptype, string machineno, string starttime, string endtime, string serviceteam, string stopreson, string status, string downcommom, string operatename, string userno)
        {
            //start/end time of the day
            try
            {
                long eventNo = OracleHelper.GetNextEventNo();
                string updatetime = DateTime.Now.ToString(Formats.GetDateFormat());
                string downTimeSql = string.Format("update {0} set {1}='{2}',{3}='{4}',{5}='{6}',{7}='{8}' where {9}='{10}'", tableName, fd_stopReason, stopreson, fd_downComment, downcommom, fd_operateName, operatename, fd_updateTime, DateTime.Now.ToString(Formats.GetDateFormat()), fd_downtimeNo, downtimeno);
                string sqlINTF_DOWNTIME = string.Format("insert into {0}" +
                    "({1}," +
                    "{2},{3},{4},{5},{6}," +
                    "{7},{8},{9},{10})" +
                    "values({11}," +
                    "'{12}','{13}','{14}','{15}','{16}'," +
                    "'{17}','{18}','{19}','{20}')",
                    L2INTF_DOWNTIME.tableName,
                    fd_eventNo,
                    fd_downtimeNo, fd_stopType, fd_machineNo, fd_startTime, fd_endTime,
                    fd_serviceTeam, fd_stopReason, fd_status, fd_downComment,
                    eventNo,
                    downtimeno, stoptype, machineno, starttime, endtime,
                    serviceteam, stopreson, status, downcommom
                    );
                string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                    L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.generateOrUpdateDowntime, updatetime, "1", userno, "");
                List<string> list = new List<string>();
                list.Add(downTimeSql);
                list.Add(sqlINTF_DOWNTIME);
                list.Add(sqlINTFEVENT);
                OracleHelper.ExecuteSqlTran(list);
                string sql = string.Format("select rownum rn,a.* from (select * from {0} where {1}='{2}') a", tableName, fd_downtimeNo, downtimeno);
                DataTable dataTable = OracleHelper.Exct_QueryTable(sql);
                return dataTable;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("Updatedowntime", ex);
                return null;
            }
        }
        /// <summary>
        /// add new downtime record
        /// </summary>
        /// <param name="predowntimeNo"></param>
        /// <param name="downtimeno"></param>
        /// <param name="stoptype"></param>
        /// <param name="machineno"></param>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <param name="serviceteam"></param>
        /// <param name="stopreson"></param>
        /// <param name="status"></param>
        /// <param name="downcommom"></param>
        /// <param name="operatename"></param>
        /// <param name="userno"></param>
        /// <returns></returns>
        public static DataTable Adddowntime(string predowntimeNo, string downtimeno, string stoptype, string machineno, string starttime, string endtime, string serviceteam, string stopreson, string status, string downcommom, string operatename, string userno)
        {
            try
            {
                long preeventNo = 0;
                string prestoptype = "0";
                string premachineno = "0";
                string prestarttime = "0";
                string preendtime = "0";
                string preserviceteam = "0";
                string prestopreason = "0";
                string prestatus = "0";
                string predowncommom = "0";

                string sql = string.Format("select * from {0} where {1}='{2}'", tableName, fd_downtimeNo, predowntimeNo);
                DataTable dataTable = OracleHelper.Exct_QueryTable(sql);
                if (dataTable != null && dataTable.Rows.Count > 0)
                {
                    preeventNo = Convert.ToInt64(dataTable.Rows[0][fd_eventNo].ToString());
                    prestoptype = dataTable.Rows[0][fd_stopType].ToString();
                    premachineno = dataTable.Rows[0][fd_machineNo].ToString();
                    prestarttime = dataTable.Rows[0][fd_startTime].ToString();
                    preendtime = dataTable.Rows[0][fd_endTime].ToString();
                    preserviceteam = dataTable.Rows[0][fd_serviceTeam].ToString();
                    prestopreason = dataTable.Rows[0][fd_stopReason].ToString();
                    prestatus = dataTable.Rows[0][fd_status].ToString();
                    predowncommom = dataTable.Rows[0][fd_downComment].ToString();
                }
                long eventNo = OracleHelper.GetNextEventNo();
                string shiftname = TEAM_TABLE.GetCurrentShift(DateTime.Now);
                string updatetime = DateTime.Now.ToString(Formats.GetDateFormat());

                string updatesql = string.Format("update {0} set {1}='{2}',{3}='{4}' where {5}='{6}'",
                    tableName, fd_endTime, starttime, fd_updateTime, updatetime, fd_downtimeNo, predowntimeNo);
                string sqlINTF_DOWNTIME = string.Format("insert into {0}" +
                    "({1}," +
                    "{2},{3},{4},{5},{6}," +
                    "{7},{8},{9},{10})" +
                    "values({11}," +
                    "'{12}','{13}','{14}','{15}','{16}'," +
                    "'{17}','{18}','{19}','{20}')",
                    L2INTF_DOWNTIME.tableName,
                    fd_eventNo,
                    fd_downtimeNo, fd_stopType, fd_machineNo, fd_startTime, fd_endTime,
                    fd_serviceTeam, fd_stopReason, fd_status, fd_downComment,
                    preeventNo,
                    predowntimeNo, prestoptype, premachineno, prestarttime, starttime,
                    preserviceteam, prestopreason, prestatus, predowncommom
                    );
                string insertsql = string.Format("insert into {0}" +
                    "({1}," +
                    "{2},{3},{4},{5},{6}," +
                    "{7},{8},{9},{10}," +
                    "{11},{12},{13})" +
                    "values({14}," +
                    "'{15}','{16}','{17}','{18}','{19}'," +
                    "'{20}','{21}','{22}','{23}'," +
                    "'{24}','{25}','{26}')",
                    tableName,
                    fd_eventNo,
                    fd_downtimeNo, fd_stopType, fd_machineNo, fd_startTime, fd_endTime,
                    fd_serviceTeam, fd_stopReason, fd_status, fd_downComment,
                    fd_operateName, fd_shiftName, fd_updateTime,
                    eventNo,
                    downtimeno, stoptype, machineno, starttime, endtime,
                    serviceteam, stopreson, status, downcommom,
                    operatename, shiftname, updatetime);
                string sqlINTF_DOWNTIMEtwo = string.Format("insert into {0}" +
                    "({1}," +
                    "{2},{3},{4},{5},{6}," +
                    "{7},{8},{9},{10})" +
                    "values({11}," +
                    "'{12}','{13}','{14}','{15}','{16}'," +
                    "'{17}','{18}','{19}','{20}')",
                    L2INTF_DOWNTIME.tableName,
                    fd_eventNo,
                    fd_downtimeNo, fd_stopType, fd_machineNo, fd_startTime, fd_endTime,
                    fd_serviceTeam, fd_stopReason, fd_status, fd_downComment,
                    eventNo,
                    downtimeno, stoptype, machineno, starttime, endtime,
                    serviceteam, stopreson, status, downcommom
                    );
                string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                    L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.generateOrUpdateDowntime, updatetime, "1", userno, "");
                List<string> list = new List<string>();
                list.Add(updatesql);
                list.Add(sqlINTF_DOWNTIME);
                list.Add(insertsql);
                list.Add(sqlINTF_DOWNTIMEtwo);
                list.Add(sqlINTFEVENT);
                OracleHelper.ExecuteSqlTran(list);

                string sqltwo = string.Format("select rownum rn,a.* from (select * from {0} where {1}='{2}' union select * from {3} where {4}='{5}') a",
                    tableName, fd_downtimeNo, predowntimeNo, tableName, fd_downtimeNo, downtimeno);
                DataTable dataTabletwo = OracleHelper.Exct_QueryTable(sqltwo);
                LogHelper.WriteLog("Adddowntime");
                return dataTabletwo;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("Adddowntime", ex);
                throw ex;
            }
        }
        #endregion
    }
}
